package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTool {

	private static final String USERNAME = "ztinfomgr";
	private static final String PASSWORD = "austitpwd";
	private static final String URL = "jdbc:mysql://211.139.127.94:24124/ztinfomgr?useUnicode=true&characterEncoding=utf-8";

//	private static final String USERNAME = "root";
//	private static final String PASSWORD = "123456";
//	private static final String URL = "jdbc:mysql://localhost/ztinfomgr";

	
	private Connection conn;
	private ResultSet rs;
	private PreparedStatement pstmt;
	
	private static DBTool tool = null;
	
	static {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("Load DB Driver failed!");
		}
	}
	private DBTool() {
		
	}
	
	public static DBTool getDBTool() {
		if (tool == null) {
			tool = new DBTool();
		}
		return tool;
	}
	public Connection getConn() {
		try{ 
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("Get DB Connection failed!");
		}
		return conn;
	}
	
	public PreparedStatement getPstmt(String sql) {
		try {
			this.getConn();
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}
	public ResultSet eQuery(String sql){
		try{
			this.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("could not query data");
		}
		return rs;
	}
	public int eUpdate(String sql) {
		int count = 0;
		try{
			this.getConn();
			pstmt = conn.prepareStatement(sql);
			count = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("could not update data");
		}		
		return count;
	}
	public void close() {
		this.close(rs);
		this.close(pstmt);
		this.closeConn();
	}
	public void closeConn() {
		if (conn == null) return;
		try {
			conn.close();
			conn = null;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void close(ResultSet rs) {
		if (rs == null) return;
		try {
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void close(Statement stmt) {
		if (stmt == null) return;
		try {
			stmt.close();
			stmt = null;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
